# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.table2Json_v2 import Table2Json

from com.zzh.utils.pagingUtils import MyPaging
from com.zzh.utils.sqlUtils.SqlUtils import SqlUtils
from com.zzh.utils.requestFieldMapDBField import fieldMap_Chen
import time
localTime = time.strftime('%Y-%m-%d', time.localtime(time.time()))


class CouponDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def insertCoupon(self, info):
        """
        添加 coupon 信息
        :param info:
        :return:
        """
        couponName = info['couponName']
        couponDescription = info['couponDescription']
        couponCode = info['couponCode']
        couponExpirationdate = info['couponExpirationdate']
        couponMemberNums = info['couponMemberNums']
        couponUsedTimes = info['couponUsedTimes']
        couponType = info['couponType']
        couponConditions = info['couponConditions']
        couponDiscount = info['couponDiscount']
        couponCreatedPerson = info['couponCreatedPerson']

        couponNeedPoints = info['couponNeedPoints']
        couponIsPoint = info['couponIsPoint']
        needPrice = info['needPrice']
        couponTopPrice = info['couponTopPrice']
        couponPercentage = info['couponPercentage']

        sql = "insert into zzh_coupon (coupon_name,coupon_description,coupon_code,coupon_expirationdate," \
              "coupon_member_nums,coupon_used_times,coupon_type,coupon_conditions,coupon_discount," \
              "coupon_created_person,coupon_need_points,coupon_is_point,need_price,coupon_top_price,coupon_percentage) VALUES " \
              "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cur = self.conn.cursor()
        try:
            rs = cur.execute(sql, [couponName, couponDescription, couponCode, couponExpirationdate, couponMemberNums,
                                   couponUsedTimes, couponType, couponConditions, couponDiscount, couponCreatedPerson,
                                   couponNeedPoints, couponIsPoint,needPrice,couponTopPrice,couponPercentage])
        except:
            return -200
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def findCouponById(self, couponId):
        """
        根据ID查询 coupon 信息
        :param couponId:
        :return:
        """
        sql = "select * from zzh_coupon WHERE coupon_id=%s"

        cur = self.conn.cursor()
        rs = cur.execute(sql, [couponId])
        jsons = None
        if rs:
            list0 = cur.fetchall()
            jsons = Table2Json.coupon_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons

    def findCouponNameById(self, couponId):
        """
        根据ID查询 coupon 信息
        :param couponId:
        :return:
        """
        sql = "select coupon_name from zzh_coupon WHERE coupon_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [couponId])
        cur.close()
        self.conn.close()
        return rs

    def findCouponNeedPointsById(self, couponId):
        """
        根据ID查询 coupon 信息
        :param couponId:
        :return:
        """
        sql = "select coupon_need_points from zzh_coupon WHERE coupon_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [couponId])
        cur.close()
        self.conn.close()
        return rs

    def findCouponByCondition(self, **kwargs):
        """
        coupon 表的条件查询
        :param kwargs:
        :return:
        """
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.couponFieldMap
        whereSentence = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM zzh_coupon WHERE coupon_expirationdate > '"+ localTime+"' AND " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM zzh_coupon WHERE coupon_expirationdate > '"+ localTime+"' AND " + whereSentence
        rs = cur.execute(sql)

        jsons = None
        if rs:
            rsSet = cur.fetchall()
            list0 = MyPaging().getPage(rs, pageIndex, pageSize, rsSet)
            jsons = Table2Json.coupon_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons, rs

    def findCouponViewByCondition(self, **kwargs):
        """
        coupon 表的条件查询
        :param kwargs:
        :return:
        """
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.couponViewFieldMap
        whereSentence = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM view_coupon WHERE status > -1" + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM view_coupon WHERE status > -1" + whereSentence

        rs = cur.execute(sql)
        jsons = None

        if rs:
            rsSet = cur.fetchall()
            list0 = MyPaging().getPage(rs, pageIndex, pageSize, rsSet)
            jsons = Table2Json.coupon_view_list_to_json(list0)
        cur.close()
        self.conn.close()
        return jsons, rs

    def updateCouponStatus(self):

        cur = self.conn.cursor()

        sql2='UPDATE zzh_coupon_used SET status=-1 WHERE (SELECT coupon_expirationdate FROM zzh_coupon WHERE zzh_coupon.coupon_id=zzh_coupon_used.coupon_id)<%s'
        res=cur.execute(sql2,[localTime])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return res

    def deleteCoupon(self, idArray):
        """
        根据ID删除信息，可以传递一个或者多个ID，ID以数组形式传递
        :param idArray:
        :return:
        """
        sql = "delete from zzh_coupon WHERE coupon_id=%s"
        cur = self.conn.cursor()
        for idd in idArray:
            rs = cur.execute(sql, [idd])
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def updateCoupon(self, info):
        """
        更新 coupon
        :param info:
        :return:
        """
        couponId = info['couponId']
        couponName = info['couponName']
        couponDescription = info['couponDescription']
        couponCode = info['couponCode']
        couponExpirationdate = info['couponExpirationdate']
        couponMemberNums = info['couponMemberNums']
        couponUsedTimes = info['couponUsedTimes']
        couponType = info['couponType']
        couponConditions = info['couponConditions']
        couponDiscount = info['couponDiscount']
        couponCreatedPerson = info['couponCreatedPerson']
        couponNeedPoints = info['couponNeedPoints']
        couponIsPoint = info['couponIsPoint']
        needPrice = info['needPrice']
        couponTopPrice = info['couponTopPrice']
        couponPercentage = info['couponPercentage']


        sql = "update zzh_coupon set coupon_name=%s,coupon_description=%s,coupon_code=%s,coupon_expirationdate=%s,coupon_member_nums=%s," \
              "coupon_used_times=%s,coupon_type=%s,coupon_conditions=%s,coupon_discount=%s,coupon_created_person=%s,coupon_need_points=%s,coupon_is_point=%s, need_price=%s,coupon_top_price=%s,coupon_percentage=%s " \
              " WHERE coupon_id=%s"
        cur = self.conn.cursor()
        try:
            rs = cur.execute(sql, [couponName, couponDescription, couponCode, couponExpirationdate, couponMemberNums,
                                   couponUsedTimes, couponType, couponConditions, couponDiscount, couponCreatedPerson,
                                   couponNeedPoints, couponIsPoint,needPrice, couponId,couponTopPrice,couponPercentage])
        except:
            return -200
        self.conn.commit()
        cur.close()
        self.conn.close()
        return rs

    def selectCoupon(self,couponId):
        """
        根据ID查询，返回元组
        :param couponId:
        :return: data tuple
        """
        sql = "select * from zzh_coupon WHERE coupon_id=%s"
        cur = self.conn.cursor()
        rs = cur.execute(sql, [couponId])
        arr = cur.fetchone()
        cur.close()
        self.conn.close()
        return arr